# Flask SQLAlchemy 复杂查询示例
# 本文件展示了各种复杂的SQL查询方法

from app.models.user import User
from app.extensions import db
from datetime import datetime, timedelta
from sqlalchemy import and_, or_, not_, func, desc, asc, text
from sqlalchemy.orm import joinedload, selectinload, contains_eager

class ComplexQueryExamples:
    """复杂查询示例类"""
    
    @staticmethod
    def basic_filtering():
        """基本过滤查询"""
        # 1. 简单条件查询
        active_users = User.query.filter_by(is_active=True).all()
        
        # 2. 使用filter进行更复杂的条件
        users_with_id_gt_10 = User.query.filter(User.id > 10).all()
        
        # 3. 多条件查询
        recent_active_users = User.query.filter(
            User.is_active == True,
            User.created_at >= datetime.now() - timedelta(days=30)
        ).all()
        
        return {
            'active_users': len(active_users),
            'users_with_id_gt_10': len(users_with_id_gt_10),
            'recent_active_users': len(recent_active_users)
        }
    
    @staticmethod
    def logical_operators():
        """逻辑运算符查询"""
        # 1. AND条件
        users = User.query.filter(
            and_(
                User.is_active == True,
                User.created_at >= datetime.now() - timedelta(days=7)
            )
        ).all()
        
        # 2. OR条件
        users = User.query.filter(
            or_(
                User.username.like('%admin%'),
                User.email.like('%admin%')
            )
        ).all()
        
        # 3. NOT条件
        users = User.query.filter(
            not_(
                User.username.like('%test%')
            )
        ).all()
        
        # 4. 复合逻辑
        users = User.query.filter(
            and_(
                User.is_active == True,
                or_(
                    User.username.like('%admin%'),
                    User.email.like('%admin%')
                ),
                not_(
                    User.created_at < datetime.now() - timedelta(days=365)
                )
            )
        ).all()
        
        return len(users)
    
    @staticmethod
    def pattern_matching():
        """模式匹配查询"""
        # 1. LIKE查询
        users = User.query.filter(User.username.like('test%')).all()  # 以test开头
        users = User.query.filter(User.email.like('%@example.com')).all()  # 以@example.com结尾
        users = User.query.filter(User.username.like('%admin%')).all()  # 包含admin
        
        # 2. ILIKE（不区分大小写）
        users = User.query.filter(User.username.ilike('%ADMIN%')).all()
        
        # 3. 正则表达式（MySQL和PostgreSQL支持）
        # users = User.query.filter(User.username.regexp('^test[0-9]+')).all()
        
        # 4. IN查询
        users = User.query.filter(User.username.in_(['admin', 'user1', 'user2'])).all()
        
        # 5. NOT IN查询
        users = User.query.filter(User.username.notin_(['test', 'demo'])).all()
        
        return len(users)
    
    @staticmethod
    def ordering_and_limiting():
        """排序和限制查询"""
        # 1. 基本排序
        users = User.query.order_by(User.username).all()  # 升序
        users = User.query.order_by(User.username.desc()).all()  # 降序
        
        # 2. 多字段排序
        users = User.query.order_by(User.is_active.desc(), User.username).all()
        
        # 3. 使用asc()和desc()函数
        users = User.query.order_by(asc(User.username), desc(User.created_at)).all()
        
        # 4. 限制结果数量
        users = User.query.limit(10).all()
        
        # 5. 分页查询
        page = 2
        per_page = 10
        users = User.query.offset((page - 1) * per_page).limit(per_page).all()
        
        # 6. 使用paginate方法（更方便的分页）
        pagination = User.query.paginate(page=page, per_page=per_page, error_out=False)
        users = pagination.items
        total_pages = pagination.pages
        total_items = pagination.total
        
        return {
            'users_count': len(users),
            'total_pages': total_pages,
            'total_items': total_items
        }
    
    @staticmethod
    def aggregation_functions():
        """聚合函数查询"""
        # 1. 计数
        user_count = User.query.count()
        active_user_count = User.query.filter(User.is_active == True).count()
        
        # 2. 使用func进行更复杂的聚合
        # 注意：这些查询通常需要使用with_entities或者直接在session上执行
        
        # 3. 计算最大ID
        max_id = db.session.query(func.max(User.id)).scalar()
        
        # 4. 计算最小ID
        min_id = db.session.query(func.min(User.id)).scalar()
        
        # 5. 计算平均ID（示例）
        avg_id = db.session.query(func.avg(User.id)).scalar()
        
        # 6. 按状态分组计数
        status_counts = db.session.query(
            User.is_active,
            func.count(User.id).label('count')
        ).group_by(User.is_active).all()
        
        return {
            'user_count': user_count,
            'active_user_count': active_user_count,
            'max_id': max_id,
            'min_id': min_id,
            'avg_id': avg_id,
            'status_counts': dict(status_counts)
        }
    
    @staticmethod
    def date_and_time_queries():
        """日期和时间查询"""
        # 1. 特定日期范围查询
        today = datetime.now().date()
        today_start = datetime.combine(today, datetime.min.time())
        today_end = datetime.combine(today, datetime.max.time())
        
        users_today = User.query.filter(
            User.created_at.between(today_start, today_end)
        ).all()
        
        # 2. 最近N天的用户
        recent_days = 7
        recent_users = User.query.filter(
            User.created_at >= datetime.now() - timedelta(days=recent_days)
        ).all()
        
        # 3. 按月份统计用户注册数量
        monthly_counts = db.session.query(
            func.extract('year', User.created_at).label('year'),
            func.extract('month', User.created_at).label('month'),
            func.count(User.id).label('count')
        ).group_by(
            func.extract('year', User.created_at),
            func.extract('month', User.created_at)
        ).order_by(
            func.extract('year', User.created_at),
            func.extract('month', User.created_at)
        ).all()
        
        return {
            'users_today': len(users_today),
            'recent_users': len(recent_users),
            'monthly_counts': len(monthly_counts)
        }
    
    @staticmethod
    def raw_sql_queries():
        """原生SQL查询"""
        # 1. 执行原生SQL查询
        result = db.session.execute(text("SELECT * FROM users WHERE is_active = :is_active"), 
                                 {'is_active': True})
        users = result.fetchall()
        
        # 2. 执行原生SQL并映射到模型
        users = db.session.query(User).from_statement(
            text("SELECT * FROM users WHERE username LIKE :username")
        ).params(username='%admin%').all()
        
        # 3. 复杂的JOIN查询（如果有其他表）
        # 示例：假设有一个orders表
        # result = db.session.execute(text("""
        #     SELECT u.*, COUNT(o.id) as order_count
        #     FROM users u
        #     LEFT JOIN orders o ON u.id = o.user_id
        #     WHERE u.is_active = :is_active
        #     GROUP BY u.id
        #     ORDER BY order_count DESC
        # """), {'is_active': True})
        
        return len(users)
    
    @staticmethod
    def subqueries():
        """子查询"""
        # 1. 使用子查询
        from sqlalchemy import exists
        
        # 查询有特定邮箱域名的用户
        subquery = db.session.query(User.id).filter(
            User.email.like('%@company.com')
        ).subquery()
        
        users = User.query.filter(
            User.id.in_(subquery)
        ).all()
        
        # 2. 使用EXISTS
        exists_query = db.session.query(
            exists().where(
                and_(
                    User.id == User.id,  # 这里应该是与其他表的关联
                    User.is_active == True
                )
            )
        )
        
        # 3. 更复杂的子查询示例
        # 查询注册时间早于平均注册时间的用户
        avg_created_at = db.session.query(
            func.avg(User.created_at)
        ).scalar()
        
        early_users = User.query.filter(
            User.created_at < avg_created_at
        ).all()
        
        return {
            'users_count': len(users),
            'early_users_count': len(early_users)
        }
    
    @staticmethod
    def case_statements():
        """CASE语句"""
        # 1. 使用CASE语句创建计算字段
        from sqlalchemy import case
        
        users_with_status = db.session.query(
            User.username,
            case(
                (User.is_active == True, '活跃'),
                (User.is_active == False, '非活跃'),
                else_='未知'
            ).label('status_text')
        ).all()
        
        # 2. 更复杂的CASE语句
        users_with_category = db.session.query(
            User.username,
            case(
                (User.created_at >= datetime.now() - timedelta(days=7), '新用户'),
                (User.created_at >= datetime.now() - timedelta(days=30), '近期用户'),
                else_='老用户'
            ).label('user_category')
        ).all()
        
        return len(users_with_status)
    
    @staticmethod
    def union_queries():
        """UNION查询"""
        # 1. 基本UNION查询
        # 注意：这里使用相同的表作为示例，实际应用中可能是不同的表
        
        active_users = db.session.query(
            User.id,
            User.username,
            User.email,
            db.literal('active').label('status')
        ).filter(User.is_active == True)
        
        inactive_users = db.session.query(
            User.id,
            User.username,
            User.email,
            db.literal('inactive').label('status')
        ).filter(User.is_active == False)
        
        # 使用UNION ALL
        all_users_status = active_users.union_all(inactive_users).all()
        
        # 2. 使用UNION（去重）
        unique_users_status = active_users.union(inactive_users).all()
        
        return len(all_users_status)
    
    @staticmethod
    def window_functions():
        """窗口函数（PostgreSQL和SQL Server支持较好）"""
        # 1. ROW_NUMBER()
        # 注意：窗口函数在不同数据库中的支持程度不同
        
        # 2. RANK()和DENSE_RANK()
        # 示例：按注册时间对用户排名
        
        # 3. LAG()和LEAD()
        # 示例：获取前一个和后一个注册的用户
        
        # 由于窗口函数的复杂性，这里提供一个简单的示例
        # 实际使用时需要根据具体数据库调整语法
        
        # 模拟窗口函数的效果
        users_with_rank = db.session.query(
            User.username,
            User.created_at,
            func.row_number().over(order_by=User.created_at).label('rank')
        ).all()
        
        return len(users_with_rank)
    
    @staticmethod
    def performance_tips():
        """查询性能优化技巧"""
        # 1. 只选择需要的字段
        users = db.session.query(User.id, User.username).all()
        
        # 2. 使用with_entities
        users = User.query.with_entities(User.id, User.username).all()
        
        # 3. 预加载关联数据（如果有关系）
        # users = User.query.options(joinedload(User.orders)).all()
        
        # 4. 使用yield_per处理大量数据
        # for user in User.query.yield_per(100):
        #     process_user(user)
        
        # 5. 索引提示（某些数据库支持）
        # users = User.query.with_hint(User, "USE INDEX (idx_username)").all()
        
        # 6. 批量更新
        # User.query.filter(User.is_active == False).update(
        #     {User.updated_at: datetime.now()}, 
        #     synchronize_session=False
        # )
        # db.session.commit()
        
        return "性能优化技巧已应用"